{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "from tqdm import tqdm"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'2022_08_19'"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "day = datetime.datetime.strftime(datetime.datetime.now(), \"%Y_%m_%d\")\n",
    "day"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 45216\r\n",
      "-rw-r--r--@ 1 ivan  staff   565464  7 30 18:56 eqList2022_07_30.xlsx\r\n",
      "-rw-r--r--@ 1 ivan  staff   566439  8  9 01:05 eqList2022_08_09.xlsx\r\n",
      "-rw-r--r--@ 1 ivan  staff  3567272  8 11 11:27 eqList2022_08_11.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff   566428  8 11 00:45 eqList2022_08_11.xlsx\r\n",
      "-rw-r--r--@ 1 ivan  staff  3568288  8 12 16:56 eqList2022_08_12.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3569648  8 13 15:09 eqList2022_08_13.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3573668  8 15 16:06 eqList2022_08_15.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3575011  8 16 18:58 eqList2022_08_16.xls\r\n",
      "-rw-r--r--@ 1 ivan  staff  3576341  8 19 17:35 eqList2022_08_19.xls\r\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'eqList/eqList2022_08_19.xls'"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "! rm -rf \"eqList/eqList\"$day\".xls\"\n",
    "! cp \"/Users/ivan/Downloads/eqList\"$day\".xls\" \"eqList/eqList\"$day\".xls\"\n",
    "! ls -l \"eqList/\"\n",
    "\n",
    "aeta = f\"eqList/eqList{day}.xls\"\n",
    "aeta"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "N = 7\n",
    "T = 10\n",
    "# 经度\n",
    "lonL, lonH = 98, 107\n",
    "# 纬度\n",
    "latL, latH = 22, 34 \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "发震时刻       object\n",
      "震级(M)     float64\n",
      "纬度(°)     float64\n",
      "经度(°)     float64\n",
      "深度(千米)    float64\n",
      "参考位置       object\n",
      "dtype: object\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>发震时刻</th>\n",
       "      <th>震级(M)</th>\n",
       "      <th>纬度(°)</th>\n",
       "      <th>经度(°)</th>\n",
       "      <th>深度(千米)</th>\n",
       "      <th>参考位置</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-08-18 15:46:27</td>\n",
       "      <td>5.4</td>\n",
       "      <td>0.30</td>\n",
       "      <td>124.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>印尼北苏拉威西省附近海域</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-08-18 10:10:21</td>\n",
       "      <td>5.3</td>\n",
       "      <td>-34.40</td>\n",
       "      <td>-70.60</td>\n",
       "      <td>100.0</td>\n",
       "      <td>智利</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-08-17 20:56:32</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-7.15</td>\n",
       "      <td>125.55</td>\n",
       "      <td>500.0</td>\n",
       "      <td>班达海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-08-17 12:24:45</td>\n",
       "      <td>5.3</td>\n",
       "      <td>33.35</td>\n",
       "      <td>142.10</td>\n",
       "      <td>10.0</td>\n",
       "      <td>日本本州东岸远海</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-08-16 10:22:55</td>\n",
       "      <td>4.3</td>\n",
       "      <td>33.14</td>\n",
       "      <td>90.27</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-08-16 10:16:53</td>\n",
       "      <td>3.0</td>\n",
       "      <td>33.04</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-08-15 21:05:09</td>\n",
       "      <td>4.1</td>\n",
       "      <td>33.12</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-08-15 16:30:37</td>\n",
       "      <td>4.7</td>\n",
       "      <td>38.43</td>\n",
       "      <td>99.26</td>\n",
       "      <td>8.0</td>\n",
       "      <td>青海海北州祁连县</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-08-15 05:10:36</td>\n",
       "      <td>4.6</td>\n",
       "      <td>37.85</td>\n",
       "      <td>93.72</td>\n",
       "      <td>10.0</td>\n",
       "      <td>青海海西州直辖区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-08-15 05:04:48</td>\n",
       "      <td>6.4</td>\n",
       "      <td>-22.00</td>\n",
       "      <td>170.95</td>\n",
       "      <td>100.0</td>\n",
       "      <td>洛亚蒂群岛</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                  发震时刻  震级(M)  纬度(°)   经度(°)  深度(千米)          参考位置\n",
       "0  2022-08-18 15:46:27    5.4   0.30  124.40   150.0  印尼北苏拉威西省附近海域\n",
       "1  2022-08-18 10:10:21    5.3 -34.40  -70.60   100.0            智利\n",
       "2  2022-08-17 20:56:32    5.6  -7.15  125.55   500.0           班达海\n",
       "3  2022-08-17 12:24:45    5.3  33.35  142.10    10.0      日本本州东岸远海\n",
       "4  2022-08-16 10:22:55    4.3  33.14   90.27    10.0      西藏那曲市安多县\n",
       "5  2022-08-16 10:16:53    3.0  33.04   90.38    10.0      西藏那曲市安多县\n",
       "6  2022-08-15 21:05:09    4.1  33.12   90.38    10.0      西藏那曲市安多县\n",
       "7  2022-08-15 16:30:37    4.7  38.43   99.26     8.0      青海海北州祁连县\n",
       "8  2022-08-15 05:10:36    4.6  37.85   93.72    10.0      青海海西州直辖区\n",
       "9  2022-08-15 05:04:48    6.4 -22.00  170.95   100.0         洛亚蒂群岛"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "import xml\n",
    "DOMTree = xml.dom.minidom.parse(aeta).documentElement\n",
    "Row = DOMTree.getElementsByTagName(\"Row\")\n",
    "\n",
    "cols, data = [], []\n",
    "for nRow, iRow in enumerate(Row):\n",
    "    if nRow == 0:\n",
    "        for jRow in iRow.getElementsByTagName(\"Cell\"):\n",
    "            cols.append(jRow.childNodes[0].childNodes[0].data)\n",
    "    else:\n",
    "        idata = []\n",
    "        for jRow in iRow.getElementsByTagName(\"Cell\"):\n",
    "            idata.append(jRow.childNodes[0].childNodes[0].data)\n",
    "        data.append([float(dj) if nj in [1,2,3,4] else str(dj) for nj ,dj in enumerate(idata)])\n",
    "\n",
    "data = pd.DataFrame(data, columns=cols)\n",
    "print(data.dtypes)\n",
    "data.head(T)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def regeo(location):\n",
    "    try:\n",
    "        import json\n",
    "        import requests\n",
    "\n",
    "        host = 'https://regeo.market.alicloudapi.com'\n",
    "        path = '/v3/geocode/regeo'\n",
    "        appcode = '830fa522a512421eaa202bb80afe8921'\n",
    "        querys = f'location={location}' # 经度在前，纬度在后\n",
    "        with requests.get(\n",
    "            f\"{host}{path}?{querys}\", \n",
    "            headers={'Authorization': f'APPCODE {appcode}'}\n",
    "        ) as r:\n",
    "            j = json.loads(r.content.decode())[\"regeocode\"][\"formatted_address\"]\n",
    "            return j\n",
    "    except:\n",
    "        return \"NaN\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "100%|██████████| 10/10 [00:13<00:00,  1.37s/it]\n"
     ]
    }
   ],
   "source": [
    "\n",
    "Kdata = []\n",
    "for STATION in tqdm(data.head(T)[\"参考位置\"]):\n",
    "    # idata\n",
    "    idata = data[data[\"参考位置\"] == STATION][[\"参考位置\", \"发震时刻\"]]\n",
    "    idata.rename({\n",
    "        \"参考位置\": \"初次参考位置\", \n",
    "        \"发震时刻\": \"初次发震时刻\",\n",
    "    }, axis=1, inplace=True)\n",
    "    idata[\"K\"] = 1\n",
    "    # print(idata.shape)\n",
    "\n",
    "    # jdata\n",
    "    jdata = data[\n",
    "        (data[\"参考位置\"] != STATION) & \n",
    "        (data[\"经度(°)\"] >= lonL) & (data[\"经度(°)\"] <= lonH) &\n",
    "        (data[\"纬度(°)\"] >= latL) & (data[\"纬度(°)\"] <= latH) &\n",
    "        # (data[\"震级(M)\"] >= 3.5) &\n",
    "        True\n",
    "    ][[\"参考位置\", \"震级(M)\", \"纬度(°)\", \"经度(°)\", \"发震时刻\"]]\n",
    "    jdata[\"K\"] = 1\n",
    "    # print(jdata.shape)\n",
    "\n",
    "    # k0data\n",
    "    k0data = pd.merge(\n",
    "        idata,\n",
    "        jdata,\n",
    "        on=\"K\"\n",
    "    )\n",
    "    # print(k0data.shape)\n",
    "\n",
    "    # k1data\n",
    "    # 筛选近N天内\n",
    "    k0data[\"gap-发震时刻\"] = (\n",
    "        pd.to_datetime(k0data[\"发震时刻\"]) - \n",
    "        pd.to_datetime(k0data[\"初次发震时刻\"])\n",
    "    ).dt.days\n",
    "\n",
    "    k1data = (k0data[\"gap-发震时刻\"] >= 1) & (k0data[\"gap-发震时刻\"] <= N)\n",
    "    # print(pd.value_counts(k1data))\n",
    "\n",
    "    k1data = k0data[k1data]\n",
    "    # print(k1data.shape)\n",
    "\n",
    "    # k2data\n",
    "    k2data = k1data.groupby(\n",
    "        [\"初次参考位置\", \"初次发震时刻\", \"K\"], \n",
    "        as_index=False\n",
    "    ).agg({\n",
    "        \"震级(M)\": \"mean\", # 特殊/余震\n",
    "        \"纬度(°)\": \"mean\",\n",
    "        \"经度(°)\": \"mean\",\n",
    "    }).groupby(\n",
    "        [\"K\"], \n",
    "        as_index=False\n",
    "    ).agg(\n",
    "        {\n",
    "        \"K\": \"sum\",\n",
    "        \"震级(M)\": \"mean\", \n",
    "        \"纬度(°)\": \"mean\",\n",
    "        \"经度(°)\": \"mean\",\n",
    "    })\n",
    "\n",
    "    k2data[\"参考位置\"] = STATION\n",
    "    k2data[\"K%\"] = k2data[\"K\"]/idata.shape[0]\n",
    "    k2data[\"震级C\"] = k2data[\"震级(M)\"].apply(lambda x: \"\" if x >= 3.5 else \"❌\")\n",
    "    k2data[\"经度C\"] = k2data[\"经度(°)\"].apply(lambda x: \"\" if x >= lonL and x <= lonH else \"❌\")\n",
    "    k2data[\"纬度C\"] = k2data[\"纬度(°)\"].apply(lambda x: \"\" if x >= latL and x <= latH else \"❌\")\n",
    "    k2data[\"经纬度解析\"] = [regeo(f\"{_1},{_2}\") for _1, _2 in zip(k2data[\"经度(°)\"], k2data[\"纬度(°)\"])]\n",
    "    k2data[\"经度(°)\"] = k2data[\"经度(°)\"].apply(lambda x: f\"{x:.6f}\")\n",
    "    k2data[\"纬度(°)\"] = k2data[\"纬度(°)\"].apply(lambda x: f\"{x:.6f}\")\n",
    "\n",
    "    k2data = k2data[\n",
    "        ['参考位置', '震级(M)', '震级C', '纬度(°)', '纬度C', '经度(°)', '经度C', 'K', 'K%','经纬度解析']\n",
    "    ]\n",
    "    # print(k2data)\n",
    "    Kdata.append(k2data)\n",
    "\n",
    "    # \n",
    "    del idata, jdata, k0data, k1data, k2data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>#发震时刻#</th>\n",
       "      <th>#震级(M)#</th>\n",
       "      <th>#纬度(°)#</th>\n",
       "      <th>#经度(°)#</th>\n",
       "      <th>#深度(千米)#</th>\n",
       "      <th>参考位置</th>\n",
       "      <th>震级(M)</th>\n",
       "      <th>震级C</th>\n",
       "      <th>纬度(°)</th>\n",
       "      <th>纬度C</th>\n",
       "      <th>经度(°)</th>\n",
       "      <th>经度C</th>\n",
       "      <th>K</th>\n",
       "      <th>K%</th>\n",
       "      <th>经纬度解析</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022-08-18 15:46:27</td>\n",
       "      <td>5.4</td>\n",
       "      <td>0.30</td>\n",
       "      <td>124.40</td>\n",
       "      <td>150.0</td>\n",
       "      <td>印尼北苏拉威西省附近海域</td>\n",
       "      <td>3.651795</td>\n",
       "      <td></td>\n",
       "      <td>29.607564</td>\n",
       "      <td></td>\n",
       "      <td>102.082769</td>\n",
       "      <td></td>\n",
       "      <td>3</td>\n",
       "      <td>0.600000</td>\n",
       "      <td>四川省甘孜藏族自治州泸定县磨西镇海螺沟冰川森林公园</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022-08-18 10:10:21</td>\n",
       "      <td>5.3</td>\n",
       "      <td>-34.40</td>\n",
       "      <td>-70.60</td>\n",
       "      <td>100.0</td>\n",
       "      <td>智利</td>\n",
       "      <td>3.281373</td>\n",
       "      <td>❌</td>\n",
       "      <td>29.369804</td>\n",
       "      <td></td>\n",
       "      <td>103.348431</td>\n",
       "      <td></td>\n",
       "      <td>17</td>\n",
       "      <td>0.459459</td>\n",
       "      <td>四川省乐山市峨眉山市龙门乡003乡道</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022-08-17 20:56:32</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-7.15</td>\n",
       "      <td>125.55</td>\n",
       "      <td>500.0</td>\n",
       "      <td>班达海</td>\n",
       "      <td>3.387615</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.126688</td>\n",
       "      <td></td>\n",
       "      <td>102.282701</td>\n",
       "      <td></td>\n",
       "      <td>23</td>\n",
       "      <td>0.766667</td>\n",
       "      <td>四川省凉山彝族自治州喜德县红莫镇格车组</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022-08-17 12:24:45</td>\n",
       "      <td>5.3</td>\n",
       "      <td>33.35</td>\n",
       "      <td>142.10</td>\n",
       "      <td>10.0</td>\n",
       "      <td>日本本州东岸远海</td>\n",
       "      <td>3.665152</td>\n",
       "      <td></td>\n",
       "      <td>28.068788</td>\n",
       "      <td></td>\n",
       "      <td>103.298788</td>\n",
       "      <td></td>\n",
       "      <td>11</td>\n",
       "      <td>0.523810</td>\n",
       "      <td>四川省凉山彝族自治州雷波县莫红乡觉吉村</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2022-08-16 10:22:55</td>\n",
       "      <td>4.3</td>\n",
       "      <td>33.14</td>\n",
       "      <td>90.27</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2022-08-16 10:22:55</td>\n",
       "      <td>4.3</td>\n",
       "      <td>33.14</td>\n",
       "      <td>90.27</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2022-08-16 10:22:55</td>\n",
       "      <td>4.3</td>\n",
       "      <td>33.14</td>\n",
       "      <td>90.27</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2022-08-16 10:16:53</td>\n",
       "      <td>3.0</td>\n",
       "      <td>33.04</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2022-08-16 10:16:53</td>\n",
       "      <td>3.0</td>\n",
       "      <td>33.04</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2022-08-16 10:16:53</td>\n",
       "      <td>3.0</td>\n",
       "      <td>33.04</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2022-08-15 21:05:09</td>\n",
       "      <td>4.1</td>\n",
       "      <td>33.12</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2022-08-15 21:05:09</td>\n",
       "      <td>4.1</td>\n",
       "      <td>33.12</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>2022-08-15 21:05:09</td>\n",
       "      <td>4.1</td>\n",
       "      <td>33.12</td>\n",
       "      <td>90.38</td>\n",
       "      <td>10.0</td>\n",
       "      <td>西藏那曲市安多县</td>\n",
       "      <td>3.318333</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.316729</td>\n",
       "      <td></td>\n",
       "      <td>101.975781</td>\n",
       "      <td></td>\n",
       "      <td>16</td>\n",
       "      <td>0.666667</td>\n",
       "      <td>四川省凉山彝族自治州德昌县热河镇团石坝</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>2022-08-15 16:30:37</td>\n",
       "      <td>4.7</td>\n",
       "      <td>38.43</td>\n",
       "      <td>99.26</td>\n",
       "      <td>8.0</td>\n",
       "      <td>青海海北州祁连县</td>\n",
       "      <td>3.230556</td>\n",
       "      <td>❌</td>\n",
       "      <td>27.474444</td>\n",
       "      <td></td>\n",
       "      <td>102.438194</td>\n",
       "      <td></td>\n",
       "      <td>6</td>\n",
       "      <td>0.857143</td>\n",
       "      <td>四川省凉山彝族自治州普格县荞窝镇大槽河螺髻九十九里风景区</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>2022-08-15 05:10:36</td>\n",
       "      <td>4.6</td>\n",
       "      <td>37.85</td>\n",
       "      <td>93.72</td>\n",
       "      <td>10.0</td>\n",
       "      <td>青海海西州直辖区</td>\n",
       "      <td>3.537143</td>\n",
       "      <td></td>\n",
       "      <td>29.816143</td>\n",
       "      <td></td>\n",
       "      <td>102.714429</td>\n",
       "      <td></td>\n",
       "      <td>7</td>\n",
       "      <td>0.777778</td>\n",
       "      <td>四川省雅安市荥经县荥河镇马草坡</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2022-08-15 05:04:48</td>\n",
       "      <td>6.4</td>\n",
       "      <td>-22.00</td>\n",
       "      <td>170.95</td>\n",
       "      <td>100.0</td>\n",
       "      <td>洛亚蒂群岛</td>\n",
       "      <td>3.185333</td>\n",
       "      <td>❌</td>\n",
       "      <td>28.826000</td>\n",
       "      <td></td>\n",
       "      <td>102.618833</td>\n",
       "      <td></td>\n",
       "      <td>25</td>\n",
       "      <td>0.781250</td>\n",
       "      <td>四川省凉山彝族自治州越西县梅花镇</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 #发震时刻#  #震级(M)#  #纬度(°)#  #经度(°)#  #深度(千米)#          参考位置  \\\n",
       "0   2022-08-18 15:46:27      5.4     0.30   124.40     150.0  印尼北苏拉威西省附近海域   \n",
       "1   2022-08-18 10:10:21      5.3   -34.40   -70.60     100.0            智利   \n",
       "2   2022-08-17 20:56:32      5.6    -7.15   125.55     500.0           班达海   \n",
       "3   2022-08-17 12:24:45      5.3    33.35   142.10      10.0      日本本州东岸远海   \n",
       "4   2022-08-16 10:22:55      4.3    33.14    90.27      10.0      西藏那曲市安多县   \n",
       "5   2022-08-16 10:22:55      4.3    33.14    90.27      10.0      西藏那曲市安多县   \n",
       "6   2022-08-16 10:22:55      4.3    33.14    90.27      10.0      西藏那曲市安多县   \n",
       "7   2022-08-16 10:16:53      3.0    33.04    90.38      10.0      西藏那曲市安多县   \n",
       "8   2022-08-16 10:16:53      3.0    33.04    90.38      10.0      西藏那曲市安多县   \n",
       "9   2022-08-16 10:16:53      3.0    33.04    90.38      10.0      西藏那曲市安多县   \n",
       "10  2022-08-15 21:05:09      4.1    33.12    90.38      10.0      西藏那曲市安多县   \n",
       "11  2022-08-15 21:05:09      4.1    33.12    90.38      10.0      西藏那曲市安多县   \n",
       "12  2022-08-15 21:05:09      4.1    33.12    90.38      10.0      西藏那曲市安多县   \n",
       "13  2022-08-15 16:30:37      4.7    38.43    99.26       8.0      青海海北州祁连县   \n",
       "14  2022-08-15 05:10:36      4.6    37.85    93.72      10.0      青海海西州直辖区   \n",
       "15  2022-08-15 05:04:48      6.4   -22.00   170.95     100.0         洛亚蒂群岛   \n",
       "\n",
       "       震级(M) 震级C      纬度(°) 纬度C       经度(°) 经度C   K        K%  \\\n",
       "0   3.651795      29.607564      102.082769       3  0.600000   \n",
       "1   3.281373   ❌  29.369804      103.348431      17  0.459459   \n",
       "2   3.387615   ❌  28.126688      102.282701      23  0.766667   \n",
       "3   3.665152      28.068788      103.298788      11  0.523810   \n",
       "4   3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "5   3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "6   3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "7   3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "8   3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "9   3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "10  3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "11  3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "12  3.318333   ❌  27.316729      101.975781      16  0.666667   \n",
       "13  3.230556   ❌  27.474444      102.438194       6  0.857143   \n",
       "14  3.537143      29.816143      102.714429       7  0.777778   \n",
       "15  3.185333   ❌  28.826000      102.618833      25  0.781250   \n",
       "\n",
       "                           经纬度解析  \n",
       "0      四川省甘孜藏族自治州泸定县磨西镇海螺沟冰川森林公园  \n",
       "1             四川省乐山市峨眉山市龙门乡003乡道  \n",
       "2            四川省凉山彝族自治州喜德县红莫镇格车组  \n",
       "3            四川省凉山彝族自治州雷波县莫红乡觉吉村  \n",
       "4            四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "5            四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "6            四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "7            四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "8            四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "9            四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "10           四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "11           四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "12           四川省凉山彝族自治州德昌县热河镇团石坝  \n",
       "13  四川省凉山彝族自治州普格县荞窝镇大槽河螺髻九十九里风景区  \n",
       "14               四川省雅安市荥经县荥河镇马草坡  \n",
       "15              四川省凉山彝族自治州越西县梅花镇  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "endata = pd.merge(\n",
    "    data.head(T).rename(\n",
    "        {icol: f\"#{icol}#\" for icol in data.columns if icol != \"参考位置\"}, \n",
    "        axis=1\n",
    "    ), \n",
    "    pd.concat(Kdata),\n",
    "    on=\"参考位置\"\n",
    ").sort_values(\"#发震时刻#\", ascending=False)\n",
    "\n",
    "endata"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      ">>> 2022-08-20 To 2022-08-26 / 2022-08-22\n",
      "\n",
      "# 有震预测 earthquake prediction\n",
      "check_my_prediction(myToken, '2022-08-20', '2022-08-22', 1, latitude=29.607564, longitude=102.082769, magnitude=3.7)\n",
      "\n",
      "\n",
      "# 有震预测 earthquake prediction\n",
      "check_my_prediction(myToken, '2022-08-20', '2022-08-22', 1, latitude=28.068788, longitude=103.298788, magnitude=3.7)\n",
      "\n",
      "\n",
      "# 有震预测 earthquake prediction\n",
      "check_my_prediction(myToken, '2022-08-20', '2022-08-22', 1, latitude=29.816143, longitude=102.714429, magnitude=3.5)\n",
      "\n",
      "\n",
      "# 无震预测 No earthquake prediction\n",
      "check_my_prediction(myToken, '2022-08-20', '2022-08-26', 0)\n",
      "\n",
      "\n"
     ]
    }
   ],
   "source": [
    "# \n",
    "day1 = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=1), \"%Y-%m-%d\")\n",
    "dayn = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=N), \"%Y-%m-%d\")\n",
    "day7 = datetime.datetime.strftime(datetime.datetime.now() + datetime.timedelta(days=7), \"%Y-%m-%d\")\n",
    "print(f\">>> {day1} To {day7} / {dayn}\")\n",
    "\n",
    "message = f\"\"\"\n",
    "# 有震预测 earthquake prediction\n",
    "check_my_prediction(myToken, '{day1}', '{dayn}', 1, latitude=$_1, longitude=$_2, magnitude=$_3)\n",
    "\"\"\"\n",
    "endata = endata[endata[\"震级C\"] != \"❌\"]\n",
    "for _1, _2, _3 in zip(endata[\"纬度(°)\"], endata[\"经度(°)\"], endata[\"震级(M)\"]):\n",
    "    print(message\n",
    "          .replace(\"$_1\",f\"{float(_1):.6f}\")\n",
    "          .replace(\"$_2\",f\"{float(_2):.6f}\")\n",
    "          .replace(\"$_3\",f\"{float(_3):.1f}\")\n",
    "         )\n",
    "\n",
    "message = f\"\"\"\n",
    "# 无震预测 No earthquake prediction\n",
    "check_my_prediction(myToken, '{day1}', '{day7}', 0)\n",
    "\"\"\"\n",
    "print(message)\n",
    "print()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "total 144\r\n",
      "-rw-r--r--@ 1 ivan  staff  34490  8 16 18:58 Test002-2022_08_16.ipynb\r\n",
      "-rw-r--r--@ 1 ivan  staff  34489  8 19 17:35 Test002-2022_08_19.ipynb\r\n"
     ]
    }
   ],
   "source": [
    "! cp Test002.ipynb \"back/Test002-\"$day\".ipynb\"\n",
    "! ls -l back/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
